/******************************************************************************
** File		: APMPermission.sql
** Description	: Script for Setting Roles & Permissions from Stored Procedures
** Params	: None
** Returns	: None
** Author	: Vineet Batta
** Date		: 03/29/2007	
** ****************************************************************************/

USE APM
GO

	SET NOCOUNT ON
		IF NOT EXISTS (SELECT NAME FROM SYSUSERS WHERE NAME = 'APM')
		BEGIN
			EXEC SP_ADDROLE [APM] 
		END
GO

	DECLARE 
		 @RowNo   	INT
		,@Routine_Name  NVARCHAR(512)
		,@RowCount 	INT

----------------------------------------------------------------------------------------
-- Execution Permission from Stored procedures & User Defined Functions 
----------------------------------------------------------------------------------------

	DECLARE  @tblRoutine TABLE
	(
		RowId INT IDENTITY(1,1) PRIMARY KEY,
		Routine_Name Nvarchar(512)
	)
	
	INSERT INTO 
		@tblRoutine(Routine_Name)
	
	SELECT 
		Routine_Name 
	FROM 
		INFORMATION_SCHEMA.ROUTINES 
	WHERE 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 
		AND 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsTableFunction') = 0
		AND 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsInlineFunction') = 0
	
	SET 	@RowNo=1
	
	SELECT 	
		@RowCount=Count(RowID) 
	FROM 
		@tblRoutine
	
	WHILE	 @RowNo<=@RowCount
	
	BEGIN
		SELECT 
			@Routine_Name=Routine_Name 
		FROM 
			@tblRoutine 
		WHERE 	
			RowID=@RowNo
	
		EXEC('GRANT EXECUTE ON dbo.['+@Routine_Name+ '] TO [APM]')
		SET @RowNo=@RowNo+1
	END
	
	----------------------------------------------------------------------------------------
	--  Select Permission for Inline & Table value Functions
	----------------------------------------------------------------------------------------
	DECLARE  @tblFunctions TABLE
	(
		RowId INT IDENTITY(1,1) PRIMARY KEY,
		Routine_Name Nvarchar(512)
	)
	
	INSERT INTO 
		@tblFunctions(Routine_Name)
	SELECT 
		Routine_Name 
	FROM 
		INFORMATION_SCHEMA.ROUTINES 
	WHERE 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 
		AND 
		(
			OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsTableFunction') = 1
			OR 
			OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsInlineFunction') = 1
		)
	
	SET 	@RowNo=1
	
	SELECT 	
		@RowCount=Count(RowID) 
	FROM 
		@tblFunctions
	
	WHILE	 @RowNo<=@RowCount
	
	BEGIN
		SELECT 
			@Routine_Name=Routine_Name 
		FROM 
			@tblFunctions 
		WHERE 	
			RowID=@RowNo
	
		EXEC('GRANT SELECT ON dbo.['+@Routine_Name+ '] TO [APM]')
		SET @RowNo=@RowNo+1
	END


GO
Print 'Setting Permission for Objects Process has Completed'